﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Column = MvvmOrm.Models.Column.Row;
using DatabaseItem = MvvmOrm.Models.DatabaseItem.Row;
using DatabaseItemParameter = MvvmOrm.Models.DatabaseItemParameter.Row;
using DatabaseModel = MvvmOrm.Models.DatabaseModel.Row;

namespace MvvmOrm.DbmsModules
{
    abstract class DbmsModuleBase
    {
        #region analysis

        public abstract void Connect(DatabaseModel model);
        public abstract void AnalyseTables(DatabaseModel model);
        public abstract void AnalyseViews(DatabaseModel model);
        public virtual void AnalyseProcedures(DatabaseModel model)
        {
        }
        public abstract void AnalyseForeignKeys(DatabaseModel model);
        public abstract void Disconnect();

        public void AnalyseDatabase(DatabaseModel model)
        {
            List<string> previously_excluded_tables = model.Tables.Where(t => !t.IsIncluded).Select(t => t.Name).ToList();
            List<string> previously_excluded_views = model.Views.Where(v => !v.IsIncluded).Select(v => v.Name).ToList();
            List<string> previously_excluded_procedures = model.Procedures.Where(p => !p.IsIncluded).Select(p => p.Name).ToList();

            foreach (var item in model.Child_DatabaseItems)
                foreach (var column in item.Child_Columns)
                    if (column.ReferencedColumnId != null)
                        column.ReferencedColumnId = null;

            foreach (var item in model.Child_DatabaseItems) {
                item.Child_DatabaseItemLogs.Clear();
                item.Child_DatabaseItemParameters.Clear();
                foreach (var column in item.Child_Columns)
                    column.Child_ColumnEnums.Clear();
                item.Child_Columns.Clear();
            }
            model.Child_DatabaseItems.Clear();

            Connect(model);
            AnalyseTables(model);
            AnalyseViews(model);
            AnalyseProcedures(model);
            AnalyseForeignKeys(model);
            Disconnect();

            foreach (var t in model.Tables) {
                if (previously_excluded_tables.Contains(t.Name))
                    t.IsIncluded = false;

                if (model.IsItemDuplicate(t.QualifiedNameSanitized)) {
                    t.CanBeIncluded = false;
                    t.LogError("Sanitized table name " + t.Name + " is not unique");
                }

                if (t.PrimaryKeys.Count() == 0)
                    t.LogWarning("This table contains no primary key");

                foreach (var c in t.Child_Columns.Where(c => t.IsColumnDuplicate(c.NameSanitized))) {
                    t.CanBeIncluded = false;
                    t.LogError("Sanitized column name " + c.Name + " is not unique");
                }

                if (!t.CanBeIncluded)
                    t.IsIncluded = false;
            }

            foreach (var v in model.Views) {
                if (previously_excluded_views.Contains(v.Name))
                    v.IsIncluded = false;

                if (model.IsItemDuplicate(v.QualifiedNameSanitized)) {
                    v.CanBeIncluded = false;
                    v.LogError("Sanitized view name " + v.Name + " is not unique");
                }

                foreach (var c in v.Child_Columns.Where(c => v.IsColumnDuplicate(c.NameSanitized))) {
                    v.CanBeIncluded = false;
                    v.LogError("Sanitized column name " + c.Name + " is not unique");
                }

                if (!v.CanBeIncluded)
                    v.IsIncluded = false;
            }

            foreach (var p in model.Procedures) {
                if (previously_excluded_procedures.Contains(p.Name))
                    p.IsIncluded = false;

                // check stored procedure name for duplicate
                if (model.IsItemDuplicate(p.QualifiedNameSanitized)) {
                    p.CanBeIncluded = false;
                    p.LogError("Sanitized procedure name " + p.Name + " is not unique");
                }

                foreach (var c in p.Child_Columns.Where(c => p.IsColumnDuplicate(c.NameSanitized))) {
                    p.CanBeIncluded = false;
                    p.LogError("Sanitized column name " + c.Name + " is not unique");
                }

                // verifify the procedure params name
                //                    foreach (DbItemProcedureParam parameter in myProc.ProcParameters)
                //                    {
                // TODO : check for duplicate parameter names
                //                    }
                if (!p.CanBeIncluded)
                    p.IsIncluded = false;
            }

        }

        #endregion

        #region generate code

        protected abstract string GetSpecificUsings();
        protected abstract void GetDbConnectionSpecifics(StringBuilder s);

        protected abstract string GetEscapeStart();
        protected abstract string GetEscapeEnd();

        protected abstract string GetCommandType();
        protected abstract string GetDataReaderType();
        protected abstract string GetConnectionType();
        protected abstract string GetParameterType();
        protected abstract string GetDbTypeType();
        protected abstract string GetTransactionType();

        protected abstract string GetAutoIncrementSqlFunction();
        protected abstract string GetDbTypeStringed(Column c);
        protected virtual string GetDbTypeStringed(DatabaseItemParameter p)
        {
            throw new NotImplementedException();
        }

        protected Dictionary<string, string> _sources;
        protected DatabaseModel _model;

        public Dictionary<string, string> GenerateCode(DatabaseModel model)
        {
            _sources = new Dictionary<string, string>();
            _model = model;

            BuildDbConnectionCode();

            foreach (var t in _model.Tables.Where(t => t.IsIncluded))
                BuildCodeTable(t);
            foreach (var v in _model.Views.Where(v => v.IsIncluded))
                BuildCodeView(v);
            foreach (var p in _model.Procedures.Where(p => p.IsIncluded))
                BuildCodeProcedure(p);

            GenerateCommonCode();

            _model = null;
            return _sources;
        }

        void GenerateCommonCode()
        {
            _sources.Add("CommandExtensions.cs",
@"using System.Collections.Generic;
using System.Linq;
" + GetSpecificUsings() + @"

namespace " + _model.Namespace + @"
{
    static class CommandExtensions
    {
        class WhereItem
        {
            public List<" + GetParameterType() + @"> Parameters = new List<" + GetParameterType() + @">();
            public string Sql = string.Empty;
        }

        static readonly string[] DbOperators = { "" = "", "" <> "", "" >= "", "" > "", "" <= "", "" < "", "" LIKE "", "" NOT LIKE "", "" IS NULL "", "" IS NOT NULL "", "" IN "", "" NOT IN "" };

        public static void AddWhereClause<T>(this " + GetCommandType() + @" command, IEnumerable<T> paramsWhere) where T : IParam
        {
            if (paramsWhere == null || paramsWhere.Count() <= 0)
                return;
            List<WhereItem> where_items = new List<WhereItem>();
            int i = 0;
            const string param_prefix = ""@W"";
            foreach (var grouped_params in from p in paramsWhere
                                           where p.DbOperator == DbOperator.IN || p.DbOperator == DbOperator.NOT_IN
                                           group p by new { Field = p.FieldFormatted, p.DbOperator }) {
                WhereItem where_item = new WhereItem();
                foreach (var param in grouped_params) {
                    var parameter = new " + GetParameterType() + @"(param_prefix + i++, param.DbType);
                    parameter.Value = param.Value;
                    where_item.Parameters.Add(parameter);
                }
                where_item.Sql = grouped_params.Key.Field + DbOperators[(int)grouped_params.Key.DbOperator]
                    + ""("" + string.Join("", "", where_item.Parameters.Select(p => p.ParameterName)) + "")"";
                where_items.Add(where_item);
            }
            foreach (var param in paramsWhere.Where(p => p.DbOperator != DbOperator.IN && p.DbOperator != DbOperator.NOT_IN)) {
                WhereItem where_item = new WhereItem();
                var parameter = new " + GetParameterType() + @"(param_prefix + i++, param.DbType);
                parameter.Value = param.Value;
                where_item.Parameters.Add(parameter);
                where_item.Sql = param.FieldFormatted + DbOperators[(int)param.DbOperator] + parameter.ParameterName;
                where_items.Add(where_item);
            }
            command.CommandText += "" WHERE "" + string.Join("" AND "", where_items.Select(w => w.Sql));
            where_items.ForEach(w => command.Parameters.AddRange(w.Parameters.ToArray()));
        }

        public static void AddOrderByClause<T>(this " + GetCommandType() + @" command, IEnumerable<T> @params) where T : IParam
        {
            if (@params == null || @params.Count() <= 0)
                return;
            command.CommandText += "" ORDER BY ""
                + string.Join("", "", @params.Select(p => p.FieldFormatted + (p.OrderByAsc ? "" ASC"" : "" DESC"")));
        }
    }
}
");

            _sources.Add("IParam.cs", GetSpecificUsings() + 
@"
namespace " + _model.Namespace + @"
{
    interface IParam
    {
        DbOperator DbOperator { get; set; }
        string FieldFormatted { get; }
        " + GetDbTypeType() + @" DbType { get; }
        object Value { get; }
        bool OrderByAsc { get; }
    }
}
");

            _sources.Add("IRow.cs",
@"using System;
using System.ComponentModel;
using System.Diagnostics.CodeAnalysis;
using System.Linq.Expressions;
using System.Reflection;

namespace " + _model.Namespace + @"
{
    public interface IRow : INotifyPropertyChanged, IDisposable
    {
    }

    public abstract class RowBase : IRow
    {
        public event PropertyChangedEventHandler PropertyChanged;

        /// <summary>
        /// Raises this object's PropertyChanged event.
        /// </summary>
        /// <param name=""propertyName"">The property that has a new value.</param>
        [SuppressMessage(""Microsoft.Design"", ""CA1030:UseEventsWhereAppropriate"", Justification = ""Method used to raise an event"")]
        protected virtual void RaisePropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = this.PropertyChanged;
            if (handler != null) {
                handler(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        /// <summary>
        /// Raises this object's PropertyChanged event for each of the properties.
        /// </summary>
        /// <param name=""propertyNames"">The properties that have a new value.</param>
        [SuppressMessage(""Microsoft.Design"", ""CA1030:UseEventsWhereAppropriate"", Justification = ""Method used to raise an event"")]
        protected void RaisePropertyChanged(params string[] propertyNames)
        {
            if (propertyNames == null) throw new ArgumentNullException(""propertyNames"");

            foreach (var name in propertyNames) {
                this.RaisePropertyChanged(name);
            }
        }

        /// <summary>
        /// Raises this object's PropertyChanged event.
        /// </summary>
        /// <typeparam name=""T"">The type of the property that has a new value</typeparam>
        /// <param name=""propertyExpression"">A Lambda expression representing the property that has a new value.</param>
        [SuppressMessage(""Microsoft.Design"", ""CA1030:UseEventsWhereAppropriate"", Justification = ""Method used to raise an event"")]
        [SuppressMessage(""Microsoft.Design"", ""CA1006:DoNotNestGenericTypesInMemberSignatures"", Justification = ""Cannot change the signature"")]
        protected void RaisePropertyChanged<T>(Expression<Func<T>> propertyExpression)
        {
            var propertyName = ExtractPropertyName(propertyExpression);
            this.RaisePropertyChanged(propertyName);
        }

        /// <summary>
        /// Extracts the property name from a property expression.
        /// </summary>
        /// <typeparam name=""T"">The object type containing the property specified in the expression.</typeparam>
        /// <param name=""propertyExpression"">The property expression (e.g. p => p.PropertyName)</param>
        /// <returns>The name of the property.</returns>
        /// <exception cref=""ArgumentNullException"">Thrown if the <paramref name=""propertyExpression""/> is null.</exception>
        /// <exception cref=""ArgumentException"">Thrown when the expression is:<br/>
        ///     Not a <see cref=""MemberExpression""/><br/>
        ///     The <see cref=""MemberExpression""/> does not represent a property.<br/>
        ///     Or, the property is static.
        /// </exception>
        [System.Diagnostics.CodeAnalysis.SuppressMessage(""Microsoft.Design"", ""CA1011:ConsiderPassingBaseTypesAsParameters""), System.Diagnostics.CodeAnalysis.SuppressMessage(""Microsoft.Design"", ""CA1006:DoNotNestGenericTypesInMemberSignatures"")]
        public static string ExtractPropertyName<T>(Expression<Func<T>> propertyExpression)
        {
            if (propertyExpression == null)
                throw new ArgumentNullException(""propertyExpression"");

            var memberExpression = propertyExpression.Body as MemberExpression;
            if (memberExpression == null)
                throw new ArgumentException(""The expression is not a member access expression."", ""propertyExpression"");

            var property = memberExpression.Member as PropertyInfo;
            if (property == null)
                throw new ArgumentException(""The member access expression does not access a property."", ""propertyExpression"");

            var getMethod = property.GetGetMethod(true);
            if (getMethod.IsStatic)
                throw new ArgumentException(""The referenced property is a static property."", ""propertyExpression"");

            return memberExpression.Member.Name;
        }

        abstract public void Dispose();
    }
}");
        }

        void GetUsings(StringBuilder s)
        {
            s.AppendLine(
@"using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
" + GetSpecificUsings());
        }

        void BuildDbConnectionCode()
        {
            StringBuilder s = new StringBuilder(100000);

            GetUsings(s);

            s.AppendLine("namespace " + _model.Namespace);
            s.AppendLine("{");

            GetDbOperatorEnumCode(s);
            GetDbConnectionClass(s);

            s.AppendLine("}");

            _sources.Add("DbConnection.cs", s.ToString());
        }

        void BuildCodeTable(DatabaseItem table)
        {
            StringBuilder s = new StringBuilder(200000);

            GetUsings(s);

            s.AppendLine("namespace " + table.QualifiedNameSanitized);
            s.AppendLine("{");

            GetFieldEnum(s, table.Child_Columns);
            GetRowClass(s, table);
            GetRowsClass(s, table);
            GetParamClass(s, table.Child_Columns);
            GetOperationClass(s, table);

            s.AppendLine("}");

            _sources.Add(table.QualifiedNameSanitized + ".cs", s.ToString());
        }

        void BuildCodeView(DatabaseItem view)
        {
            StringBuilder s = new StringBuilder(200000);

            GetUsings(s);

            s.AppendLine("namespace " + view.QualifiedNameSanitized);
            s.AppendLine("{");

            GetFieldEnum(s, view.Child_Columns);
            GetRowClass(s, view);
            GetRowsClass(s, view);
            GetParamClass(s, view.Child_Columns);
            GetOperationClass(s, view);

            s.AppendLine("}");

            _sources.Add(view.QualifiedNameSanitized + ".cs", s.ToString());
        }

        void BuildCodeProcedure(DatabaseItem proc)
        {
            StringBuilder s = new StringBuilder(200000);

            GetUsings(s);

            s.AppendLine("namespace " + proc.QualifiedNameSanitized);
            s.AppendLine("{");

            if (proc.Child_Columns.Count > 0) {
                GetFieldEnum(s, proc.Child_Columns);
                GetRowClass(s, proc);
                GetRowsClass(s, proc);
            }

            GetProcedureOperationClass(s, proc);

            s.AppendLine("}");

            _sources.Add(proc.QualifiedNameSanitized + ".cs", s.ToString());

        }

        void GetFieldEnum(StringBuilder s, IEnumerable<Column> columns)
        {
            s.AppendLine(string.Format(@"
    /// <summary>
    /// Fields of this database item
    /// </summary>
    public enum Field
    {{
{0}
    }}", string.Join(",\r\n", columns.Select(c => "        @" + c.NameSanitized))));
        }

        void GetDbConnectionClass(StringBuilder s)
        {
            s.Append(@"
    /// <summary>Wraps a connection to the database</summary>
    /// <remarks>
    /// It can be declare alone, and used across different Operation class
    /// instance of different database items by setting their DbConnection property
    /// to it. This allows the use of transactions with more than one database item at a time.
    /// </remarks>
    public partial class DbConnection : IDisposable
    {
        public const string EscapeStart = """ + GetEscapeStart() + @""";
        public const string EscapeEnd = """ + GetEscapeEnd() + @""";

        #region DBMS specifics");

            GetDbConnectionSpecifics(s);

            s.Append(@"
        #endregion

        bool _autoConnect;
        int _commandTimeout;
        bool _autoConnected;

        internal " + GetConnectionType() + @" _connection;
        " + GetTransactionType() + @" _transaction;

        /// <summary>Gets or sets if the connection should auto-connect.</summary>
        /// <remarks>
        /// If AutoConnect is true, the connection doesn't have to be open
        /// to interact with the database. The connection will then open and
        /// close automatically.
        /// If AutoConnect is true, but the DbConnection is manually opened,
        /// then the connection will not automatically close after a database
        /// operation occurs. It will remain open.
        /// </remarks>
        public bool AutoConnect
        {
            get { return _autoConnect; }
            set { _autoConnect = value; }
        }
      
        /// <summary>
        /// Gets or sets the timeout for each executed data command.
        /// </summary>
        public int CommandTimeout
        {
            get { return _commandTimeout; }
            set { _commandTimeout = value; }
        }
            
        /// <summary>Gets the current state of the connection</summary>
        public ConnectionState ConnectionState
        {
            get { return _connection.State; }
        }

        public void Dispose()
        {
            if (_connection != null)
                _connection.Dispose();
        }

        void Initalize()
        {
            _autoConnect = true;
            _autoConnected = false;
            _commandTimeout = 30;
            _connection = new " + GetConnectionType() + @" ();
        }
            
        /// <summary>
        /// Closes the connection to the database.
        /// </summary>
        public void Close()
        {
            _connection.Close();
        }

        /// <summary>
        /// Begins a database transaction.
        /// </summary>
        /// <exception cref=""System.InvalidOperationException"">
        /// Parallel transactions are not supported.
        /// </exception>
        public void BeginTransaction()
        {
            ManageAutoConnect();
            _transaction = _connection.BeginTransaction();
        }

        /// <summary>
        /// Begins a database transaction with the current System.Data.IsolationLevel value.
        /// </summary>
        /// <param name=""isolationLevel"">The transaction isolation level for this connection.</param>
        /// <exception cref=""System.InvalidOperationException"">
        /// Parallel transactions are not supported.
        /// </exception>
        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            ManageAutoConnect();
            _transaction = _connection.BeginTransaction(isolationLevel);
        }

        /// <summary>
        /// Commits the database transaction.
        /// </summary>
        /// <exception cref=""System.Exception"">
        /// An error occurred while trying to commit the transaction.
        /// </exception>
        /// <exception cref=""System.InvalidOperationException"">
        /// The transaction has already been committed or rolled back.
        /// -or- The connection is broken.
        /// </exception>
        public void CommitTransaction()
        {
            _transaction.Commit();
            _transaction = null;
            ManageAutoDisconnect();
        }  

        /// <summary>
        /// Rolls back a transaction from a pending state.
        /// </summary>
        /// <exception cref=""System.Exception"">
        /// An error occurred while trying to commit the transaction.
        /// </exception>
        /// <exception cref=""System.InvalidOperationException"">
        /// The transaction has already been committed or rolled back.
        /// -or- The connection is broken.
        /// </exception>
        public void RollbackTransaction()
        {
            _transaction.Rollback();
            _transaction = null;
            ManageAutoDisconnect();
        }

        /// <summary>Executes a command</summary>
        /// <param name=""command"">The command to execute.</param>
        /// <returns>Number of rows affected</returns>
        public int ExecuteNonQuery(string sql)
        {
            using (var command = new " + GetCommandType() + @"(sql))
                return ExecuteNonQuery(command);
        }    

        /// <summary>Executes a command</summary>
        /// <param name=""command"">The command to execute.</param>
        /// <returns>Number of rows affected</returns>
        public int ExecuteNonQuery(" + GetCommandType() + @" command)
        {
            int ret;
            ManageAutoConnect();
            command.Connection = _connection;
            command.CommandTimeout = _commandTimeout;
            
            if (_transaction != null)
                command.Transaction = _transaction;
            ret = command.ExecuteNonQuery();
            ManageAutoDisconnect();
            return ret;
        }
            
        /// <summary>Executes a list of commands</summary>
        /// <param name=""commands"">The commands to execute.</param>
        /// <returns>Total number of rows affected</returns>
        public int ExecuteNonQuery(IEnumerable<" + GetCommandType() + @"> commands)
        {
            int ret = 0;
            ManageAutoConnect();
            foreach (" + GetCommandType() + @" command in commands)
                ret += ExecuteNonQuery(command);
            ManageAutoDisconnect();
            return ret;
        } 

        /// <summary>Executes a command</summary>
        /// <param name=""command"">The command to execute.</param>
        /// <returns>First value of the first record in the result set</returns>
        public object ExecuteScalar(string sql)
        {
            using (var command = new " + GetCommandType() + @"(sql))
                return ExecuteScalar(command);
        }    

        /// <summary>Executes a command</summary>
        /// <param name=""command"">The command to execute.</param>
        /// <returns>First value of the first record in the result set</returns>
        public object ExecuteScalar(" + GetCommandType() + @" command)
        {
            object ret;
            ManageAutoConnect();
            command.Connection = _connection;
            command.CommandTimeout = _commandTimeout;
            
            if (_transaction != null)
                command.Transaction = _transaction;
            ret = command.ExecuteScalar();
            ManageAutoDisconnect();
            return ret;
        }   

        public " + GetDataReaderType() + @" ExecuteReader(string sql)
        {
            using (var command = new " + GetCommandType() + @"(sql))
                return ExecuteReader(command);
        }   

        /// <summary>
        /// Executes a command against this instance of DbConnection
        /// and returns a DataReader of the resulting set of rows.
        /// </summary>
        /// <param name=""command"">The command to execute</param>
        /// <returns>The DataReader</returns>
        /// <remarks>
        /// This method requires the connection to be already opened, even if
        /// AutoConnect is set to true
        /// </remarks>
        public " + GetDataReaderType() + @" ExecuteReader(" + GetCommandType() + @" command)
        {
            command.Connection = _connection;
            command.CommandTimeout = _commandTimeout;
            
            if (_transaction != null)
                command.Transaction = _transaction;
            return command.ExecuteReader();
        }
            
        internal void ManageAutoConnect()
        {
    	    if (_autoConnect  && _connection.State != ConnectionState.Open) {
    		    Open();
    		    _autoConnected = true;
    	    }
        }
            
        internal void ManageAutoDisconnect()
        {
    	    if (_autoConnected && _transaction == null
                && _connection.State == ConnectionState.Open)
                Close();
            if (_transaction != null && _transaction.Connection != null)
                return;
    	    _autoConnected = false;
        }
    }
");
        }

        void GetRowClass(StringBuilder s, DatabaseItem table)
        {
            var primaryKeys = table.PrimaryKeys.ToList();

            s.AppendLine(@"
    public partial class Row : RowBase
    {
        #region private variables

        internal DbConnection _dbConnection;
        internal bool _inMovement = false;
");

            foreach (var c in table.Child_Columns)
                s.AppendLine("        internal " + c.TypeWithNullableSign + " _" + c.NameSanitized + " = " + c.DefaultValueStringed + ";");

            foreach (var c in table.Child_Columns.Where(c => !c.IsReadOnly && c.IsPrimaryKey))
                s.AppendLine("        internal " + c.TypeWithNullableSign + " _old_" + c.NameSanitized + " = " + c.DefaultValueStringed + ";");

            foreach (var fk_group in from fk in table.ParentForeignKeys group fk by fk.ReferencedTable)
                s.AppendLine("        internal " + fk_group.Key.QualifiedNameSanitized + ".Row _parent_" + fk_group.Key.NameSanitized + ";");

            foreach (var fk_group in from fk in table.ChildForeignKeys group fk by fk.ConstrainedTable)
                s.AppendLine("        " + fk_group.Key.QualifiedNameSanitized + ".Rows _child_" + fk_group.Key.NameSanitized + "s = null;");

            s.AppendLine(@"
        #endregion

        #region properties
");

            foreach (var c in table.Child_Columns) {
                if (primaryKeys.Count == 1 && primaryKeys[0].IsAutoIncrement && c.Type == typeof(byte[])) {

                    s.AppendLine("        public long @" + c.NameSanitized + "_length");
                    s.AppendLine("        {");
                    s.AppendLine("            get {");
                    s.AppendLine("                if (_dbConnection == null)");
                    s.AppendLine("                    return @" + c.NameSanitized + ".Length;");
                    s.AppendLine("                else");
                    s.AppendLine("                    return (long)_dbConnection.ExecuteScalar(\"SELECT LENGTH(" + Escape(c.Name) + ") FROM " + Escape(table.Schema, table.Name) + " WHERE " + Escape(primaryKeys[0].Name) + "=\" + " + primaryKeys[0].NameSanitized + ");");
                    s.AppendLine("            }");
                    s.AppendLine("        }");

                    s.AppendLine("        bool _" + c.NameSanitized + "_loaded = false;");
                }
                s.AppendLine("        public " + c.TypeWithNullableSign + " @" + c.NameSanitized);
                s.AppendLine("        {");
                s.AppendLine("            get {");

                if (primaryKeys.Count == 1 && primaryKeys[0].IsAutoIncrement && c.Type == typeof(byte[])) {
                    s.AppendLine("                if (!_" + c.NameSanitized + "_loaded) {");
                    s.AppendLine("                    if (_dbConnection != null)");
                    s.AppendLine("                        _" + c.NameSanitized + " = (byte[])_dbConnection.ExecuteScalar(\"SELECT " + Escape(c.Name) + " FROM " + Escape(table.Schema, table.Name) + " WHERE " + Escape(primaryKeys[0].Name) + "=\" + " + primaryKeys[0].NameSanitized + ");");
                    s.AppendLine("                    _" + c.NameSanitized + "_loaded = true;");
                    s.AppendLine("                }");
                }

                s.AppendLine("                return _" + c.NameSanitized + ";");
                s.AppendLine("            }");
                s.AppendLine("            set {");
                s.AppendLine("                if (value != @" + c.NameSanitized + ") {");
                s.AppendLine("                    _" + c.NameSanitized + " = value;");
                if (primaryKeys.Count > 0 && !table.IsReadOnly) {
                    s.AppendLine("                    if (_dbConnection != null)");
                    s.AppendLine("                        Operation.Update(_dbConnection, this, Field.@" + c.NameSanitized + ");");
                }
                s.AppendLine("                    RaisePropertyChanged(\"" + c.NameSanitized + "\");");

                if (primaryKeys.Count == 1 && primaryKeys[0].IsAutoIncrement && c.Type == typeof(byte[]))
                    s.AppendLine("                    RaisePropertyChanged(\"" + c.NameSanitized + "_length\");");

                s.AppendLine("                }");
                s.AppendLine("            }");
                s.AppendLine("        }");
            }
            s.AppendLine();

            // treeview binding stuff
            if (!table.Child_Columns.Any(c => c.NameSanitized.Equals("isselected", StringComparison.InvariantCultureIgnoreCase)))
                s.AppendLine(@"
        bool _isSelected;
        public bool IsSelected
        {
            get { return _isSelected; }
            set {
                if (value == _isSelected)
                    return;
                _isSelected = value;
                RaisePropertyChanged(() => this.IsSelected);
            }
        }");

            if (!table.Child_Columns.Any(c => c.NameSanitized.Equals("isexpanded", StringComparison.InvariantCultureIgnoreCase)))
                s.AppendLine(@"
        bool _isExpanded;
        public bool IsExpanded
        {
            get { return _isExpanded; }
            set {
                if (value == _isExpanded)
                    return;
                _isExpanded = value;
                RaisePropertyChanged(() => this.IsExpanded);
            }
        }
");

            foreach (var fk_group in from fk in table.ParentForeignKeys group fk by fk.ReferencedTable) {
                s.AppendLine(String.Format(@"
        public {0}.Row Parent_{1}
        {{
            get {{ return _parent_{1}; }}
        }}

        public void MoveTo_{1}({0}.Row destination)
        {{
            _inMovement = true;
            _parent_{1}.Child_{2}s.Remove(this);
{3}
            destination.Child_{2}s.Add(this);
            _inMovement = false;
        }}",
                fk_group.Key.QualifiedNameSanitized,
                fk_group.Key.NameSanitized,
                table.NameSanitized,
                String.Join("\r\n", from fk in fk_group
                                    select "            " + fk.ConstrainedColumn.NameSanitized + " = destination." + fk.ReferencedColumn.NameSanitized + ";")));
            }

            foreach (var fk_group in from fk in table.ChildForeignKeys group fk by fk.ConstrainedTable)
                s.AppendLine(string.Format(@"
        public List<{0}.Param> Child_{1}s_OrderBy {{ get; set; }}
        public {0}.Rows Child_{1}s
        {{
            get
            {{
                if (_child_{1}s == null) {{
                    if (_dbConnection == null)
                        _child_{1}s = new {0}.Rows(Child_{1}s_OrderBy);
                    else {{
                        _child_{1}s = {0}.Operation.Select(_dbConnection, this, Child_{1}s_OrderBy, true);
                        foreach (var row in _child_{1}s)
                            row._parent_{2} = this;
                    }}
                    _child_{1}s.Parent_{2} = this;
                }}
                return _child_{1}s;
            }}
        }}", fk_group.Key.QualifiedNameSanitized, fk_group.Key.NameSanitized, table.NameSanitized));

            s.AppendLine(@"
        public IComparable this[Field field]
        {
            get
            {
                switch (field) {");
            foreach (var field in from c in table.Child_Columns
                                  where c.Type.GetInterfaces().Contains(typeof(IComparable))
                                  select c.NameSanitized) {
                s.AppendLine(string.Format(
@"                case Field.@{0}:
                    return @{0};", field));
            }

            s.AppendLine(@"
                }
                throw new InvalidEnumArgumentException(
                            ""The field "" + field + "" doesn't implement IComparable"");
            }
        }

        #endregion

        #region constructors / destructors

        partial void PostInitialize();
        partial void PreDispose();
        partial void OnPropertyChanged(string propertyName);

        void Row_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            OnPropertyChanged(e.PropertyName);
        }

        public override void Dispose()
        {
            PreDispose();
            PropertyChanged -= new PropertyChangedEventHandler(Row_PropertyChanged);

            if (_dbConnection != null)
                _dbConnection = null;
");
            foreach (var fk_group in from fk in table.ParentForeignKeys group fk by fk.ReferencedTable)
                s.AppendLine("            if (_parent_" + fk_group.Key.NameSanitized + " != null) _parent_" + fk_group.Key.NameSanitized + " = null;");
            foreach (var fk_group in from fk in table.ChildForeignKeys group fk by fk.ConstrainedTable)
                s.AppendLine("            if (_child_" + fk_group.Key.NameSanitized + "s != null) _child_" + fk_group.Key.NameSanitized + "s = null;");
            s.AppendLine(@"
        }

        /// <summary>
        /// Inititalizes a new instance of the Row class
        /// using the specified values for the different fields.
        /// </summary>
        public Row (");
            s.AppendLine(string.Join(",\r\n", table.Child_Columns.Select(c =>
                "            " + c.TypeWithNullableSign + " @" + c.NameSanitized)) + ",");
            s.AppendLine(@"DbConnection conn = null) {");

            foreach (var c in table.Child_Columns)
                s.AppendLine("            _" + c.NameSanitized + " = @" + c.NameSanitized + ";");
            s.AppendLine(@"
            _dbConnection = conn;
            PropertyChanged += new PropertyChangedEventHandler(Row_PropertyChanged);
            PostInitialize();
       }

        /// <summary>
        /// Inititalizes a new instance of the Row class
        /// </summary>
        public Row(DbConnection conn = null)
        {");
            foreach (var c in table.Child_Columns)
                s.AppendLine("            _" + c.NameSanitized + " = " + c.DefaultValueStringed + ";");
            s.AppendLine(@"
            _dbConnection = conn;
            PropertyChanged += new PropertyChangedEventHandler(Row_PropertyChanged);
            PostInitialize();
        }

        #endregion

        #region public methods

        /// <summary>
        /// Duplicates this instance of Row
        /// </summary>
        /// <returns>The new copy of Row</returns>
        public Row Clone(bool copyDbConnection = false)
        {
            return new Row(");

            foreach (var c in table.Child_Columns)
                s.AppendLine("            @" + c.NameSanitized + ",");

            s.AppendLine(@"            (copyDbConnection ? _dbConnection : null));
        }

        #endregion
    }");
        }

        void GetRowsClass(StringBuilder s, DatabaseItem item)
        {
            if (item.IsReadOnly) {
                s.AppendLine(@"
    public class Rows : ReadOnlyCollection<Row>
    {
        public Rows(IList<Row> rows) : base(rows) 
        { 
        }
    }
");
                return;
            }
            s.AppendLine(@"
    public class Rows : ObservableCollection<Row>, IDisposable
    {
        DbConnection _dbConnection;
        List<Param> _orderBys;

        IEnumerable<Param> OrderBys
        {
            get { return _orderBys; }
            set { _orderBys = (value == null ? null : new List<Param>(value)); }
        }");

            //internal Lesson.Row Parent_Lesson { get; set; }
            foreach (var fk_group in from fk in item.ParentForeignKeys group fk by fk.ReferencedTable)
                s.AppendLine("        internal " + fk_group.Key.QualifiedNameSanitized + ".Row Parent_" + fk_group.Key.NameSanitized + " { get; set; }");

            s.AppendLine(@"
        public Rows(IEnumerable<Param> orderBys = null)
            : base() 
        {
            OrderBys = orderBys;
        }

        public Rows(IEnumerable<Row> rows, IEnumerable<Param> orderBys = null) 
            : base(rows) 
        { 
            OrderBys = orderBys;

            foreach (Row row in rows)
                row.PropertyChanged += new PropertyChangedEventHandler(row_PropertyChanged);
        }

        internal Rows(DbConnection conn, IEnumerable<Param> orderBys = null)
            : base()
        {
            OrderBys = orderBys;
            _dbConnection = conn;
        }

        internal Rows(DbConnection conn, IEnumerable<Row> rows, IEnumerable<Param> orderBys = null)
            : base(rows)
        {
            OrderBys = orderBys;
            _dbConnection = conn;

            foreach (Row row in rows)
                row.PropertyChanged += new PropertyChangedEventHandler(row_PropertyChanged);
        }

        void row_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            Row row = sender as Row;
            if (row == null || OrderBys == null)
                return;
            if (OrderBys.Any(o => o.Field.ToString() == e.PropertyName))
                Move(this.IndexOf(row), GetOrderedIndex(row));
        }

        bool RowIsAfter(Row row, Row baseRow)
        {
            foreach (int i in _orderBys.Select(o => (o.OrderByAsc ? 1 : -1) * row[o.Field].CompareTo(baseRow[o.Field])))
                if (i == 0)
                    continue;
                else 
                    return i > 0;
            return false;
        }

        int GetOrderedIndex(Row row)
        {
            int index = 0;
            foreach (Row row2 in this.Where(r => r != row))
                if (RowIsAfter(row, row2))
                    index++;
                else
                    return index;
            return index;
        }

        protected override void InsertItem(int index, Row row)
        {");

            //if (Parent_Lesson != null) {
            //    row._parent_Lesson = Parent_Lesson;
            //    row._lesson_id = Parent_Lesson._id;
            //}
            foreach (var fk_group in from fk in item.ParentForeignKeys group fk by fk.ReferencedTable) {
                s.AppendLine(String.Format(@"
            if (Parent_{0} != null) {{
                row._parent_{0} = Parent_{0};
                {1}
                if (!row._inMovement && Parent_{0}._dbConnection != null) {{
                    Parent_{0}._dbConnection.Insert(row);
                    row._dbConnection = Parent_{0}._dbConnection;
                }}
            }} 
            else",
                fk_group.Key.NameSanitized,
                String.Join("\r\n",
                    from fk in fk_group
                    select "row._" + fk.ConstrainedColumn.NameSanitized + " = Parent_" + fk_group.Key.NameSanitized + "._" + fk.ReferencedColumn.NameSanitized + ";")));
            }

            s.AppendLine(@"
            if (_dbConnection != null && !row._inMovement) {
                _dbConnection.Insert(row);
                row._dbConnection = _dbConnection;
            }

            index = _orderBys == null || _orderBys.Count == 0 ? index : GetOrderedIndex(row);

            row.PropertyChanged += new PropertyChangedEventHandler(row_PropertyChanged);
            base.InsertItem(index, row);
        }

        protected override void RemoveItem(int index)
        {
            Row row = this[index];
            if (!row._inMovement) {");

            if (item.PrimaryKeys.Count() > 0 && !item.IsReadOnly) {
                foreach (var fk_group in from fk in item.ParentForeignKeys group fk by fk.ReferencedTable)
                    s.AppendLine(String.Format(@"
                if (Parent_{0} != null) {{
                    if (Parent_{0}._dbConnection != null)
                        Parent_{0}._dbConnection.Delete(row);
                }}
                else", fk_group.Key.NameSanitized));

                s.AppendLine(@"
                if (row._dbConnection != null)
                    _dbConnection.Delete(row);");
            }

            s.AppendLine(@"
                row.Dispose();
            }
            row.PropertyChanged -= new PropertyChangedEventHandler(row_PropertyChanged);
            base.RemoveItem(index);
        }

        protected override void ClearItems()
        {
            foreach (Row row in this) {");

            if (item.PrimaryKeys.Count() > 0 && !item.IsReadOnly) {
                foreach (var fk_group in from fk in item.ParentForeignKeys group fk by fk.ReferencedTable)
                    s.AppendLine(String.Format(@"
                if (Parent_{0} != null) {{
                    if (Parent_{0}._dbConnection != null)
                        Parent_{0}._dbConnection.Delete(row);
                }}
                else", fk_group.Key.NameSanitized));
                s.AppendLine(@"
                if (row._dbConnection != null)
                    _dbConnection.Delete(row);");
            }

            s.AppendLine(@"
                row.PropertyChanged -= new PropertyChangedEventHandler(row_PropertyChanged);
                row.Dispose();
            }
            base.ClearItems();
        }
        
        public void Dispose()
        {");

            foreach (var fk_group in from fk in item.ParentForeignKeys group fk by fk.ReferencedTable)
                s.AppendLine("            Parent_" + fk_group.Key.NameSanitized + " = null;");

            s.AppendLine(@"
            _dbConnection = null;
        }
    }
");
        }

        void GetParamClass(StringBuilder s, IEnumerable<Column> columns)
        {
            s.AppendLine(@"
    /// <summary>
    /// Represents a parameter to use with methods of the class Operation.
    /// A Param object represents one Field of the database item.
    /// </summary>
    public class Param : IParam
    {
        #region private variables

        string _fieldExact;
        DbOperator _dbOperator;
        Field _field;
        bool _orderByAsc;
        DbType _dbType;
        object _value;

        #endregion

        #region properties

        /// <summary>
        /// Gets the Field that this instance of Param represents database item.
        /// </summary>
        public Field Field
        {
            get { return _field; }
        }

        /// <summary>
        /// Gets the exact name of the field formatted for SQL queries
        /// </summary>
        public string FieldFormatted
        {
            get { return DbConnection.EscapeStart + _fieldExact + DbConnection.EscapeEnd; }
        }

        /// <summary>
        /// Gets or sets the value of this instance of Param
        /// </summary>
        /// <exception cref=""System.InvalidCastException"">Thrown when the value provided cannot be 
        /// converted to the type of the field.</exception>
        public object Value
        {
            get { return _value; }
            set
            {
                switch (_field)
                {");

            foreach (var c in columns) {
                s.AppendLine("                    case Field.@" + c.NameSanitized + ":");
                s.AppendLine("                        _value = Convert.ChangeType(value, typeof(" + c.TypeStringed + "));");
                s.AppendLine("                        break;");
            }

            s.AppendLine(@"
                }
            }
        }

        /// <summary>
        /// Gets the DbType of the field represented by this instance of Param.
        /// </summary>
        public DbType DbType
        {
            get { return _dbType; }
        }

        /// <summary>
        /// Gets or sets the DbOperator that will be used for mean of
        /// comparison when this instance of Param is used in a Where clause.
        /// </summary>
        public DbOperator DbOperator
        {
            get { return _dbOperator; }
            set { _dbOperator = value; }
        }

        /// <summary>
        /// Gets or sets the the sort direction for when this Param instance is used 
        /// in an Order By clause.
        /// </summary>
        public bool OrderByAsc
        {
            get { return _orderByAsc; }
            set { _orderByAsc = value; }
        }

        #endregion

        #region constructors / destructors

        /// <summary>
        /// Inititalizes a new instance of the Param class using the specified Field name.
        /// </summary>
        /// <param name=""field"">The Field that this instance of Param will represent</param>
        public Param(Field field) 
            : this(field, DbOperator.EQUAL, null)
        {
        }

        /// <summary>
        /// Inititalizes a new instance of the Param class using the 
        /// specified Field name and the sort direction used in an Order By clause.
        /// </summary>
        /// <param name=""field"">The Field that this instance of Param will represent</param>
        /// <param name=""orderByAsc"">The sort direction for when this instance of Param 
        /// is used in an Order By clause</param>
        public Param(Field field, bool orderByAsc)
        {
            InitParam(field);
            _orderByAsc = orderByAsc;
        }

        /// <summary>
        /// Inititalizes a new instance of the Param class
        /// using the specified Field name and the comparison operator for when used in a Where clause.
        /// </summary>
        /// <param name=""field"">The Field that this instance of Param will represent</param>
        /// <param name=""dbOperator"">The comparison operator for when this instance of Param 
        /// is used in a Where clause</param>
        public Param(Field field, DbOperator dbOperator) 
            : this(field, dbOperator, null)
        {
        }

        /// <summary>
        /// Inititalizes a new instance of the Param class 
        /// using the specified Field name, the comparison operator for when
        /// used in a Where clause and the value it initially holds.
        /// </summary>
        /// <param name=""field"">The Field that this instance of Param will represent</param>
        /// <param name=""dbOperator"">The comparison operator for when this instance of Param 
        /// is used in a Where clause</param>
        /// <param name=""value"">The value that this instance of Param initially holds</param>
        public Param(Field field, DbOperator dbOperator, object value)
        {
            InitParam(field);
            if (value != null)
                Value = value;
            _dbOperator = dbOperator;
            _orderByAsc = true;
        }

        #endregion

        #region private functions

        void InitParam(Field field)
        {
            switch (field)
            {");

            foreach (var c in columns) {
                s.AppendLine("                case Field.@" + c.NameSanitized + ":");
                s.AppendLine("                    _fieldExact = \"" + c.Name + "\";");
                s.AppendLine("                    _dbType = DbType." + GetDbTypeStringed(c) + ";");
                s.AppendLine("                    _value = (" + c.TypeWithNullableSign + ")" + c.DefaultValueStringed + ";");
                s.AppendLine("                    break;");
            }

            s.AppendLine(@"
            }
            _field = field;
        }

        #endregion
    }
");
        }

        void GetDbOperatorEnumCode(StringBuilder s)
        {
            s.AppendLine(
@"    /// <summary>
    /// Specifies an operator for a Param object of a database
    /// item included in this assembly.
    /// </summary>
    public enum DbOperator
    {
        EQUAL = 0,
        NOT_EQUAL,
        GREATER_OR_EQUAL,
        GREATER,
        LESS_OR_EQUAL,
        LESS,
        LIKE,
        NOT_LIKE,
        IS_NULL,
        IS_NOT_NULL,
        IN,
        NOT_IN
    }
");
        }

        void GetProcedureOperationClass(StringBuilder s, DatabaseItem proc)
        {
            s.Append(@"
    /// <summary>Handles all interaction with the database using a DbConnection</summary>
    public static class Operation
    {
        #region public methods

        /// <summary>Invokes the stored procedure</summary>
        public static " + (proc.Child_Columns.Count > 0 ? "Rows" : "void") + @" Invoke(this DbConnection conn");

            if (proc.Child_DatabaseItemParameters.Count > 0)
                s.Append(", " + string.Join(", ", proc.Child_DatabaseItemParameters.Select(p => {
                    switch (p.ParameterDirectionEnum) {
                    case ParameterDirection.Input:
                        return p.TypeStringed + " @" + p.NameSanitized;
                    case ParameterDirection.InputOutput:
                        return "ref " + p.TypeStringed + " @" + p.NameSanitized;
                    case ParameterDirection.Output:
                        return "out " + p.TypeStringed + " @" + p.NameSanitized;
                    default:
                        throw new Exception("unknown ParameterDirection: " + p.ParameterDirectionEnum.ToString());
                    }
                })));

            s.AppendLine(@")
        {
            var command = new " + GetCommandType() + @"();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = """ + Escape(proc.Schema, proc.Name) + @""";
            " + GetParameterType() + " parameter;");

            foreach (var p in proc.Child_DatabaseItemParameters) {
                s.AppendLine("            parameter = new " + GetParameterType() + "(\"" + p.Name + "\", DbType." + GetDbTypeStringed(p) + ");");
                switch (p.ParameterDirectionEnum) {
                case ParameterDirection.Input:
                    s.AppendLine("            parameter.Direction = ParameterDirection.Input;");
                    s.AppendLine("            parameter.Value = @" + p.NameSanitized + ";");
                    break;
                case ParameterDirection.InputOutput:
                    s.AppendLine("            parameter.Direction = ParameterDirection.InputOutput;");
                    s.AppendLine("            parameter.Value = @" + p.NameSanitized + ";");
                    break;
                case ParameterDirection.Output:
                    s.AppendLine("            parameter.Direction = ParameterDirection.Output;");
                    break;
                }
                s.AppendLine("            command.Parameters.Add(parameter);");
            }

            if (proc.Child_Columns.Count > 0)
                s.AppendLine("            return new Rows(conn.Select(command).ToList());");
            else
                s.AppendLine("            conn.ExecuteNonQuery(command);");

            foreach (var p in proc.Child_DatabaseItemParameters.Where(_ => _.ParameterDirectionEnum == ParameterDirection.InputOutput 
                    || _.ParameterDirectionEnum == ParameterDirection.Output))
                s.Append("            @" + p.NameSanitized + " = (" + p.TypeStringed + ")command.Parameters[\"" + p.Name + "\"].Value;");

            s.AppendLine(@"
        }

        #endregion;");

            if (proc.Child_Columns.Count > 0) {
                s.AppendLine(@"
        static IEnumerable<Row> Select(this DbConnection conn, " + GetCommandType() + @" command)
        {
            conn.ManageAutoConnect();
            using (var dr = conn.ExecuteReader(command)) {
                while (dr.Read()) {
                    Row row = new Row(");

                int readerIndex = 0;
                foreach (var c in proc.Child_Columns) {
                    s.Append("                    ");
                    string dr_function = GetDataReaderFunction(c.Type);

                    if (c.Nullable)
                        s.AppendLine("dr.IsDBNull(" + readerIndex + ") ? (" + c.TypeWithNullableSign + ")null : " +
                            (dr_function == null ? "(" + c.TypeStringed + ")dr[" + readerIndex + "],"
                                                 : "dr." + dr_function + "(" + readerIndex + "),"));
                    else
                        s.AppendLine(dr_function == null ? "(" + c.TypeStringed + ")dr[" + readerIndex + "],"
                                                         : "dr." + dr_function + "(" + readerIndex + "),");
                    readerIndex++;
                }

                s.AppendLine("                    conn);");

                foreach (var c in proc.Child_Columns.Where(c => !c.IsReadOnly && c.IsPrimaryKey))
                    s.AppendLine("                    row._old_" + c.NameSanitized + " = row._" + c.NameSanitized + ";");

                s.AppendLine(@"
                    yield return row;
                }
                dr.Close();
            }
            conn.ManageAutoDisconnect();
        }");
            }

            s.AppendLine(@"
    }");
        }

        void GetOperationClass(StringBuilder s, DatabaseItem item)
        {
            s.AppendLine(@"
    /// <summary>Handles all interaction with the database using a DbConnection</summary>
    public static class Operation
    {
        #region public methods");

            GetOperationMinMaxCountCode(s, item);

            GetOperationSelectCode(s, item);

            if (!item.IsReadOnly) {
                GetOperationInsertCode(s, item);

                GetOperationUpdateCode(s, item);

                GetOperationDeleteCode(s, item);
            }

            s.AppendLine(@"
        #endregion
    }");
        }

        void GetOperationInsertCode(StringBuilder s, DatabaseItem table)
        {
            s.AppendLine(@"
        /// <summary>Inserts a Row</summary>
        /// <param name=""row"">Row to insert</param>
        public static void Insert(this DbConnection conn, Row row)
        {
            Insert(conn, new[] { row });
        }

        /// <summary>Inserts a list of Rows</summary>
        /// <param name=""rows"">Row list to insert.</param>
        public static void Insert(this DbConnection conn, IEnumerable<Row> rows)
        {
            using (var command = new " + GetCommandType() + @"()) {");

            s.AppendLine("                command.CommandText = \"INSERT INTO " + Escape(table.Schema, table.Name) + " ("
                            + String.Join(", ", table.NotReadOnly.Select(c => Escape(c.Name)))
                            + ") VALUES ("
                            + String.Join(", ", table.NotReadOnly.Select(c => "@" + c.NameSanitized))
                            + ");\";");

            if (table.AutoIncrement != null)
                s.AppendLine("                command.CommandText += \"SELECT " + GetAutoIncrementSqlFunction() + ";\";");

            foreach (var c in table.NotReadOnly)
                s.AppendLine("                command.Parameters.Add(new " + GetParameterType() + "(\"@" + c.NameSanitized
                            + "\", DbType." + GetDbTypeStringed(c) + "));");

            s.AppendLine("                foreach (Row row in rows) {");
            s.AppendLine("                    row._dbConnection = conn;");

            foreach (var c in table.NotReadOnly) {
                s.Append("                    command.Parameters[\"@" + c.NameSanitized + "\"].Value = ");
                if (!c.Nullable)
                    s.AppendLine("row._" + c.NameSanitized + ";");
                else
                    s.AppendLine("row._" + c.NameSanitized + " ?? (object)DBNull.Value;");
            }

            foreach (var c in table.Child_Columns.Where(c => !c.IsReadOnly && c.IsPrimaryKey))
                s.AppendLine("                    row._old_" + c.NameSanitized + " = row._" + c.NameSanitized + ";");

            if (table.AutoIncrement != null)
                s.AppendLine("                    row._" + table.AutoIncrement.NameSanitized + " = (" + table.AutoIncrement.TypeStringed + ")conn.ExecuteScalar(command);");
            else
                s.AppendLine("                    conn.ExecuteNonQuery(command);");

            s.AppendLine(@"
                }
            }
        }

        /// <summary>Inserts a row</summary>
        /// <param name=""@params"">Values to insert in the new row</param>
        public static void Insert(this DbConnection conn, IEnumerable<Param> @params)
        {
            Row row = new Row();
            foreach (Param param in @params)
                switch (param.Field) {");

            foreach (var c in table.Child_Columns) {
                s.AppendLine("                    case Field.@" + c.NameSanitized + ":");
                s.AppendLine("                        row._" + c.NameSanitized + " = (" + c.TypeStringed + ")param.Value;");
                s.AppendLine("                        break;");
            }

            s.AppendLine(@"
                }
            Insert(conn, row);
        }");
        }

        void GetOperationUpdateCode(StringBuilder s, DatabaseItem table)
        {
            // UPDATE functions that rely on primary keys
            if (table.PrimaryKeys.Count() > 0) {

                List<Column> columnsSet = table.Child_Columns.Where(c => !c.IsReadOnly).ToList();
                List<Column> columnsWhere = table.Child_Columns.Where(c => c.IsPrimaryKey).ToList();

                s.AppendLine(@"
        /// <summary>Updates a Row</summary>
        /// <param name=""row"">The Row to update</param>
        /// <returns>1 if a row was updated, 0 otherwise</returns>
        public static int Update(this DbConnection conn, Row row)
        {
            return Update(conn, new[] { row });
        }

        public static int Update(this DbConnection conn, IEnumerable<Row> rows)
        {
            int ret = 0;
            using (var command = new " + GetCommandType() + @"()) {");

                s.AppendLine("                command.CommandText = \"UPDATE " + Escape(table.Schema, table.Name) + " SET "
                    + String.Join(", ", columnsSet.Select(c => Escape(c.Name) + "=@" + c.NameSanitized))
                    + " WHERE "
                    + String.Join(" AND ", columnsWhere.Select(c => Escape(c.Name) + "=@" + c.NameSanitized)) + ";\";");


                foreach (var c in columnsSet)
                    s.AppendLine("                command.Parameters.Add(new " + GetParameterType() + "(\"@" + c.NameSanitized
                                + "\", DbType." + GetDbTypeStringed(c) + "));");

                foreach (var c in columnsWhere)
                    s.AppendLine("                command.Parameters.Add(new " + GetParameterType() + "(\"@" + c.NameSanitized
                                + "\", DbType." + GetDbTypeStringed(c) + "));");

                s.AppendLine("                foreach (Row row in rows) {");

                foreach (var c in columnsSet) {
                    s.Append("                    command.Parameters[\"@" + c.NameSanitized + "\"].Value = ");
                    if (!c.Nullable)
                        s.AppendLine("row._" + c.NameSanitized + ";");
                    else
                        s.AppendLine("row._" + c.NameSanitized + " ?? (object)DBNull.Value;");
                }

                foreach (var c in columnsWhere) {
                    s.Append("                    command.Parameters[\"@" + c.NameSanitized + "\"].Value = ");
                    if (!c.IsReadOnly) {
                        if (!c.Nullable)
                            s.AppendLine("row._old_" + c.NameSanitized + ";");
                        else
                            s.AppendLine("row._old_" + c.NameSanitized + " ?? (object)DBNull.Value;");
                    }
                    else {
                        if (!c.Nullable)
                            s.AppendLine("row._" + c.NameSanitized + ";");
                        else
                            s.AppendLine("row._" + c.NameSanitized + " ?? (object)DBNull.Value;");
                    }
                }

                s.AppendLine("                    ret += conn.ExecuteNonQuery(command);");

                foreach (var c in table.Child_Columns.Where(c => !c.IsReadOnly && c.IsPrimaryKey))
                    s.AppendLine("                    row._old_" + c.NameSanitized + " = row._" + c.NameSanitized + ";");

                s.AppendLine(@"
                }
                return ret;
            }
        }

        internal static int Update(this DbConnection conn, Row row, Field field)
        {");

                s.AppendLine("            const string sql = \"UPDATE " + Escape(table.Schema, table.Name) + " SET \\\"{0}\\\"=@S0 WHERE "
                    + String.Join(" AND ", columnsWhere.ConvertAll(c => Escape(c.Name) + "=@" + c.NameSanitized)) + ";\";");

                s.AppendLine("            using (var command = new " + GetCommandType() + @"()) {");
                s.AppendLine("                string column = String.Empty;");
                s.AppendLine("                " + GetParameterType() + @" parameter = null;");
                s.AppendLine("                switch (field) {");
                foreach (var c in columnsSet) {
                    s.AppendLine("                case Field.@" + c.NameSanitized + ":");
                    s.AppendLine("                    parameter = new " + GetParameterType() + "(\"@S0\", DbType." + GetDbTypeStringed(c) + ");");
                    if (c.Nullable) {
                        s.AppendLine("                    if (row._" + c.NameSanitized + " == null)");
                        s.AppendLine("                        parameter.Value = DBNull.Value;");
                        s.AppendLine("                    else");
                        s.AppendLine("                        parameter.Value = row._" + c.NameSanitized + ";");
                    }
                    else
                        s.AppendLine("                    parameter.Value = row._" + c.NameSanitized + ";");
                    s.AppendLine("                    column = \"" + c.Name + "\";");
                    s.AppendLine("                    break;");
                }

                s.AppendLine("                }");
                s.AppendLine("                command.Parameters.Add(parameter);");
                s.AppendLine("                command.CommandText = String.Format(sql, column);");
                s.AppendLine();

                foreach (var c in columnsWhere) {
                    s.AppendLine("                parameter = new " + GetParameterType() + "(\"@" + c.NameSanitized + "\", DbType." + GetDbTypeStringed(c) + ");");

                    if (!c.IsReadOnly) {
                        if (c.Nullable) {
                            s.AppendLine("                    if (row._old_" + c.NameSanitized + " == null)");
                            s.AppendLine("                        parameter.Value = DBNull.Value;");
                            s.AppendLine("                    else");
                            s.AppendLine("                        parameter.Value = row._old_" + c.NameSanitized + ";");
                        }
                        else
                            s.AppendLine("                    parameter.Value = row._old_" + c.NameSanitized + ";");
                    }
                    else {
                        if (c.Nullable) {
                            s.AppendLine("                    if (row._" + c.NameSanitized + " == null)");
                            s.AppendLine("                        parameter.Value = DBNull.Value;");
                            s.AppendLine("                    else");
                            s.AppendLine("                        parameter.Value = row._" + c.NameSanitized + ";");
                        }
                        else
                            s.AppendLine("                    parameter.Value = row._" + c.NameSanitized + ";");
                    }

                    s.AppendLine("                command.Parameters.Add(parameter);");
                    s.AppendLine();
                }

                foreach (var c in table.Child_Columns.Where(c => !c.IsReadOnly && c.IsPrimaryKey))
                    s.AppendLine("                row._old_" + c.NameSanitized + " = row._" + c.NameSanitized + ";");

                s.AppendLine();
                s.AppendLine("                return conn.ExecuteNonQuery(command);");
                s.AppendLine("            }");
                s.AppendLine("        }");
                s.AppendLine();
            }

            // "classic" SET/WHERE UPDATE functions

            s.AppendLine(@"
        /// <summary>
        /// Updates all rows of the database item by setting a field specified by the Param provided.
        /// </summary>
        /// <param name=""paramSet"">The Param used to update the corresponding
        /// field in the database item.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, Param paramSet)
        {
            return Update(conn, paramSet, new List<Param>());
        }

        /// <summary>
        /// Updates all rows of the database item by setting the fields specified by
        /// the Param collection provided. 
        /// </summary>
        /// <param name=""paramsSet"">The collection of Param used to update the corresponding
        /// fields in the database item.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, IEnumerable<Param> paramsSet)
        {
            return Update(conn, paramsSet, new List<Param>());
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name=""paramSet"">The Param object that specifies the field to update.</param>
        /// <param name=""paramWhere"">The Param object that specifies the field
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, Param paramSet, Param paramWhere)
        {
            List<Param> paramsSet = new List<Param>();
            List<Param> paramsWhere = new List<Param>();
            paramsSet.Add(paramSet);
            paramsWhere.Add(paramWhere);
            return Update(conn, paramsSet, paramsWhere);
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name=""paramSet"">The Param object that specifies the field
        /// to update.</param>
        /// <param name=""paramsWhere"">The Param collection object that specifies the fields
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, Param paramSet, IEnumerable<Param> paramsWhere)
        {
            List<Param> paramsSet = new List<Param>();
            paramsSet.Add(paramSet);
            return Update(conn, paramsSet, paramsWhere);
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects 
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name=""paramsSet"">The Param collection object that specifies the fields
        /// to update.</param>
        /// <param name=""paramWhere"">The Param object that specifies the field
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, IEnumerable<Param> paramsSet, Param paramWhere)
        {
            List<Param> paramsWhere = new List<Param>();
            paramsWhere.Add(paramWhere);
            return Update(conn, paramsSet, paramsWhere);
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects 
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name=""paramsSet"">The Param collection object that specifies the fields
        /// to update.</param>
        /// <param name=""paramsWhere"">The Param collection object that specifies the fields
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(DbConnection conn, IEnumerable<Param> paramsSet, IEnumerable<Param> paramsWhere)
        {
            using (var command = new " + GetCommandType() + @"()) {
                " + GetParameterType() + @" parameter;
                int i = 0;
                command.CommandText = ""UPDATE " + Escape(table.Schema, table.Name) + @" SET "" + string.Join("", "",
                   (from p in paramsSet select p).Select(p => {
                       parameter = new " + GetParameterType() + @"(""@"" + i++, p.DbType);
                       parameter.Value = p.Value ?? (object)DBNull.Value;
                       command.Parameters.Add(parameter);
                       return p.FieldFormatted + ""=@"" + i;
                   }));
                command.AddWhereClause(paramsWhere);
                return conn.ExecuteNonQuery(command);
            }
        }");
        }

        void GetOperationDeleteCode(StringBuilder s, DatabaseItem table)
        {
            s.AppendLine(@"
        /// <summary>Deletes all rows</summary>
        /// <returns>The number of deleted rows.</returns>
        public static int Delete(this DbConnection conn)
        {
            return Delete(conn, new List<Param>());
        }");

            // TODO: handle primary keys with null values for Delete and Insert.....
            // DELETE functions that rely on primary keys
            if (table.PrimaryKeys.Count() > 0) {
                var primaryKeys = table.PrimaryKeys.ToList();

                s.AppendLine(@"
        /// <summary>
        /// Delete specified rows (the primary key is used to match the rows to delete)
        /// </summary>
        /// <param name=""rows"">The list of rows to delete</param>
        /// <returns>The number of affected rows.</returns>
        public static int Delete(this DbConnection conn, IEnumerable<Row> rows)
        {
            int i = 0;
            foreach (Row row in rows)
                i += Delete(conn, row);
            return i;
        }

        /// <summary>
        /// Delete the specified row (the primary key is used to match the row to delete)
        /// </summary>
        /// <param name=""row"">The row to delete.</param>
        /// <returns>The number of affected rows.</returns>
        public static int Delete(this DbConnection conn, Row row)
        {
            List<Param> @params = new List<Param>();");

                foreach (var c in primaryKeys.Select(_ => _.NameSanitized))
                    s.AppendLine("            @params.Add(new Param(Field.@" + c + ", DbOperator.EQUAL, row._" + c + "));");

                s.AppendLine();
                s.AppendLine("            return Delete(conn, @params);");
                s.AppendLine("        }");
                s.AppendLine();

                s.AppendLine("        /// <summary>");
                s.AppendLine("        /// Deletes the row in the database item using the specified primary key");
                s.AppendLine("        /// as the search criteria");
                s.AppendLine("        /// </summary>");

                foreach (var c in primaryKeys.Select(_ => _.PrimaryKeyName))
                    s.AppendLine("        /// <param name=\"" + c + "\">Value of the primary key</param>");

                s.AppendLine("        /// <returns>1 if a row was deleted, 0 otherwise</returns>");
                s.AppendLine("        public static int Delete(this DbConnection conn, "
                    + string.Join(", ", primaryKeys.Select(c => c.PrimaryKeyDeclaration))
                    + ")");
                s.AppendLine("        {");
                s.AppendLine("            List<Param>  @params = new List<Param>();");
                s.AppendLine();

                foreach (var c in primaryKeys)
                    s.AppendLine("            @params.Add(new Param(Field.@" + c.NameSanitized
                        + ", DbOperator.EQUAL, " + c.PrimaryKeyName + "));");

                s.AppendLine();
                s.AppendLine("            return Delete(conn, @params);");
                s.AppendLine("        }");
                s.AppendLine();
                for (int i = primaryKeys.Count - 1; i > 0; i--) {
                    s.AppendLine("        /// <summary>Delete the rows for the specified partial primary key</summary>");
                    s.AppendLine("        public static int Delete(this DbConnection conn, "
                        + string.Join(", ", primaryKeys.Where(c => primaryKeys.IndexOf(c) < i).Select(c => c.PrimaryKeyDeclaration))
                        + ")");
                    s.AppendLine("        {");
                    s.AppendLine("            List<Param> @params = new List<Param>();");
                    s.AppendLine();

                    foreach (var c in primaryKeys.Where(_ => primaryKeys.IndexOf(_) < i))
                        s.AppendLine("            @params.Add(new Param(Field.@"
                            + c.NameSanitized + ", DbOperator.EQUAL, " + c.PrimaryKeyName + "));");

                    s.AppendLine();
                    s.AppendLine("            return Delete(conn, @params);");
                    s.AppendLine("        }");
                    s.AppendLine();
                }
            }

            foreach (var fk_group in from fk in table.ParentForeignKeys group fk by fk.ReferencedTable) {

                s.AppendLine("        /// <summary>Delete the rows referenced by the specified parent row</summary>");
                s.AppendLine("        public static int DeleteFromRef(this DbConnection conn, " + fk_group.Key.QualifiedNameSanitized + ".Row referencedRow)");
                s.AppendLine("        {");
                s.AppendLine("            List<Param> @params = new List<Param>();");

                foreach (var fk in fk_group)
                    s.AppendLine("            @params.Add(new Param(Field." + fk.ConstrainedColumn.NameSanitized + "));");

                int i = 0;
                foreach (var fk in fk_group)
                    s.AppendLine("            @params[" + i++ + "].Value = referencedRow." + fk.ReferencedColumn.NameSanitized + ";");

                s.AppendLine("            return Delete(conn, @params);");
                s.AppendLine("        }");
                s.AppendLine();
            }

            s.AppendLine(@"
        /// <summary>
        /// Deletes the rows in the database item using the specified Param as the search criteria.
        /// </summary>
        /// <param name=""paramWhere"">The search criteria.</param>
        /// <returns>The number of deleted rows.</returns>
        public static int Delete(this DbConnection conn, Param paramWhere)
        {
            List<Param> @params = new List<Param>();
            @params.Add(paramWhere);
            return Delete(conn, @params);
        }

        /// <summary>
        /// Deletes the rows in the database item using the specified
        /// collection of Param as the search criteria
        /// </summary>
        /// <param name=""paramsWhere"">The set of search criteria.</param>
        /// <returns>The number of deleted rows.</returns>
        public static int Delete(this DbConnection conn, IEnumerable<Param> paramsWhere)
        {
            using (var command = new " + GetCommandType() + @"()) {
                command.CommandText = ""DELETE FROM " + Escape(table.Schema, table.Name) + @" "";
                command.AddWhereClause(paramsWhere);
                return conn.ExecuteNonQuery(command);
            }
        }");
        }

        void GetOperationSelectCode(StringBuilder s, DatabaseItem item)
        {
            var t = item;
            var primaryKeys = item.PrimaryKeys.ToList();
            if (primaryKeys.Count > 0) {
                s.AppendLine("        /// <summary>Select the row with the specified primary key</summary>");

                foreach (var c in primaryKeys)
                    s.AppendLine("        /// <param name=\"" + c.PrimaryKeyName + "\">Primary key value</param>");

                s.AppendLine("        /// <returns>The row for the primary key if it exists, null otherwise</returns>");
                s.AppendLine("        public static Row Select(this DbConnection conn, "
                    + string.Join(", ", item.PrimaryKeys.Select(c => c.PrimaryKeyDeclaration))
                    + ")");
                s.AppendLine("        {");
                s.AppendLine("            List<Param> @params = new List<Param>(" + primaryKeys.Count + ");");

                foreach (var c in primaryKeys)
                    s.AppendLine("            @params.Add(new Param(Field.@" + c.NameSanitized + ", DbOperator.EQUAL, " + c.PrimaryKeyName + "));");

                s.AppendLine("            Rows rows = Select(conn, @params);");
                s.AppendLine("            return rows.Count == 1 ? rows[0] : null;");
                s.AppendLine("        }");
                s.AppendLine();

                for (int i = primaryKeys.Count - 1; i > 0; i--) {
                    s.AppendLine("        /// <summary>");
                    s.AppendLine("        /// Select the rows for the specified partial primary key, ordered by the unspecfied part of the primary key");
                    s.AppendLine("        /// </summary>");
                    s.AppendLine("        public static Rows Select(this DbConnection conn, "
                        + string.Join(", ", primaryKeys.FindAll(c => primaryKeys.IndexOf(c) < i).ConvertAll(c => c.PrimaryKeyDeclaration))
                        + ")");
                    s.AppendLine("        {");
                    s.AppendLine("            List<Param> paramsWhere = new List<Param>();");
                    s.AppendLine("            List<Param> paramsOrderBy = new List<Param>();");
                    s.AppendLine();

                    primaryKeys.FindAll(c => primaryKeys.IndexOf(c) < i).ForEach(c =>
                        s.AppendLine("            paramsWhere.Add(new Param(Field.@"
                            + c.NameSanitized + ", DbOperator.EQUAL, " + c.PrimaryKeyName + "));"));

                    primaryKeys.FindAll(c => primaryKeys.IndexOf(c) >= i).ForEach(c =>
                        s.AppendLine("            paramsOrderBy.Add(new Param(Field.@" + c.NameSanitized + "));"));

                    s.AppendLine();
                    s.AppendLine("            return Select(conn, paramsWhere, paramsOrderBy);");
                    s.AppendLine("        }");
                    s.AppendLine();
                }
            }

            s.AppendLine(@"
        /// <summary>
        /// Select all rows.
        /// </summary>
        /// <param name=""conn""></param>
        /// <returns>Selected rows</returns>
        public static Rows Select(this DbConnection conn, bool linkWithDb = false)
        {
            return Select(conn, paramsWhere: null, paramsOrderBy: null, linkWithDb: linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name=""paramWhere"">The Param for the Where clause</param>
        /// <param name=""paramOrderBy"">The Param for the Order By clause</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, Param paramWhere, Param paramOrderBy, bool linkWithDb = false)
        {
            return Select(conn, 
                paramWhere == null ? null : new[] { paramWhere },
                paramOrderBy == null ? null : new[] { paramOrderBy },
                linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name=""paramsWhere"">The Param list for the Where clause</param>
        /// <param name=""paramOrderBy"">The Param for the Order By clause</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, IEnumerable<Param> paramsWhere, Param paramOrderBy, bool linkWithDb = false)
        {
            return Select(conn, 
                paramsWhere, 
                paramOrderBy == null ? null : new[] { paramOrderBy },
                linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name=""paramWhere"">The Param for the Where clause</param>
        /// <param name=""paramsOrderBy"">The Param list for the Order By clause</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, Param paramWhere, IEnumerable<Param> paramsOrderBy, bool linkWithDb = false)
        {
            return Select(conn, 
                paramWhere == null ? null : new[] { paramWhere },
                paramsOrderBy,
                linkWithDb);
        }");

            foreach (var fk_group in from fk in item.ParentForeignKeys group fk by fk.ReferencedTable) {
                s.AppendLine("        public static Rows Select(this DbConnection conn, IEnumerable<" + fk_group.Key.QualifiedNameSanitized + ".Row> referencedRows, "
                                    + "IEnumerable<Param> paramsOrderBy, bool linkWithDb = false)");
                s.AppendLine("        {");
                s.AppendLine("            List<Row> rows = new List<Row>();");
                s.AppendLine("            foreach (var referencedRow in referencedRows)");
                s.AppendLine("                foreach (var row in conn.Select(referencedRow, paramsOrderBy, linkWithDb))");
                s.AppendLine("                    rows.Add(row);");
                s.AppendLine("            return new Rows(rows);");
                s.AppendLine("        }");
                s.AppendLine();

                s.AppendLine("        public static Rows Select(this DbConnection conn, " + fk_group.Key.QualifiedNameSanitized + ".Row referencedRow, "
                                    + "IEnumerable<Param> paramsOrderBy, bool linkWithDb = false)");
                s.AppendLine("        {");
                s.AppendLine("            List<Param> paramsWhere = new List<Param>();");

                foreach (var fk in fk_group)
                    s.AppendLine("            paramsWhere.Add(new Param(Field." + fk.ConstrainedColumn.NameSanitized + "));");

                int i = 0;
                foreach (var fk in fk_group)
                    s.AppendLine("            paramsWhere[" + i++ + "].Value = referencedRow." + fk.ReferencedColumn.NameSanitized + ";");

                s.AppendLine("            return conn.Select(paramsWhere, paramsOrderBy, linkWithDb);");
                s.AppendLine("        }");
                s.AppendLine();
            }

            s.AppendLine(@"
        /// <summary>Select rows</summary>
        /// <param name=""paramsWhere"">The Param list for the Where clause</param>
        /// <param name=""paramsOrderBy"">The Param list for the Order By clause</param>
        /// <param name=""linkWithDb"">Insert/Delete in database when adding/removing rows.</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, IEnumerable<Param> paramsWhere, IEnumerable<Param> paramsOrderBy = null, bool linkWithDb = false)
        {");
            s.AppendLine("            using (var command = new " + GetCommandType() + "(\"SELECT "
                + string.Join(", ", item.Child_Columns.Where(c => !(primaryKeys.Count == 1 && primaryKeys[0].IsAutoIncrement && c.Type == typeof(byte[]))).Select(c => Escape(c.Name)))
                + " FROM " + Escape(item.Schema, item.Name) + " \")) {");
            s.AppendLine(@"
                command.AddWhereClause(paramsWhere);
                command.AddOrderByClause(paramsOrderBy);
");
            if (item.IsReadOnly)
                s.AppendLine(@"
                return new Rows(conn.Select(command).ToList());
");
            else
                s.AppendLine(@"
                if (linkWithDb)
                    return new Rows(conn, conn.Select(command), paramsOrderBy);
                else
                    return new Rows(conn.Select(command), paramsOrderBy);
");
            s.AppendLine(@"
            }
        }

        internal static IEnumerable<Row> Select(this DbConnection conn, " + GetCommandType() + @" command)
        {
            conn.ManageAutoConnect();
            using (var dr = conn.ExecuteReader(command)) {
                while (dr.Read()) {
                    Row row = new Row(");

            int readerIndex = 0;
            foreach (var c in t.Child_Columns) {
                s.Append("                    ");

                if (primaryKeys.Count == 1 && primaryKeys[0].IsAutoIncrement && c.Type == typeof(byte[])) {
                    s.AppendLine("null,");
                    continue;
                }

                string dr_function = GetDataReaderFunction(c.Type);

                if (c.Nullable)
                    s.AppendLine("dr.IsDBNull(" + readerIndex + ") ? (" + c.TypeWithNullableSign + ")null : " +
                        (dr_function == null ? "(" + c.TypeStringed + ")dr[" + readerIndex + "],"
                                             : "dr." + dr_function + "(" + readerIndex + "),"));
                else
                    s.AppendLine(dr_function == null ? "(" + c.TypeStringed + ")dr[" + readerIndex + "],"
                                                     : "dr." + dr_function + "(" + readerIndex + "),");
                readerIndex++;
            }

            s.AppendLine("                    conn);");

            foreach (var c in t.Child_Columns.Where(c => !c.IsReadOnly && c.IsPrimaryKey))
                s.AppendLine("                    row._old_" + c.NameSanitized + " = row._" + c.NameSanitized + ";");

            s.AppendLine(@"
                    yield return row;
                }
                dr.Close();
            }
            conn.ManageAutoDisconnect();
        }");
        }

        void GetOperationMinMaxCountCode(StringBuilder s, DatabaseItem item)
        {
            s.AppendLine(@"
        /// <summary>Get Max of a field of this DB item</summary>
        /// <param name=""field"">The Field for which to find the maximum value</param>
        /// <returns>The maximum value found or DbNull</returns>
        public static object Max(this DbConnection conn, Field field)
        {
            List<Param> @params = new List<Param>();
            return Max(conn, field, @params);
        }

        /// <summary>Get Max of a field of this DB itemusing a Param for the Where clause</summary>
        /// <param name=""field"">The Field for which to find the maximum value</param>
        /// <param name=""param"">The Param object used for the Where clause</param>
        /// <returns>The maximum value found or DbNull</returns>
        public static object Max(this DbConnection conn, Field field, Param param)
        {
            List<Param> @params = new List<Param>();
            @params.Add(param);
            return Max(conn, field, @params);
        }

        /// <summary>
        /// Get the maximum value for a specified field
        /// of this database item using a collection of Param for the Where clause
        /// </summary>
        /// <param name=""field"">The Field for which to find the maximum value</param>
        /// <param name=""@params"">The Param collection object used for the 
        /// Where clause</param>
        /// <returns>The maximum value found or DbNull</returns>
        public static object Max(this DbConnection conn, Field field, IEnumerable<Param> @params)
        {
            using (var command = new " + GetCommandType() + @"()) {
                Param param = new Param(field);
                command.CommandText = ""SELECT MAX("" + param.FieldFormatted + "") FROM " + Escape(item.Schema, item.Name) + @" "";
                command.AddWhereClause(@params);
                return conn.ExecuteScalar(command);
            }
        }

        /// <summary>Get the miminum value for a specified field of this database item </summary>
        /// <param name=""field"">The Field for which to find the minimum value</param>
        /// <returns>The minimum value found or DbNull</returns>
        public static object Min(this DbConnection conn, Field field)
        {
            List<Param> @params = new List<Param>();
            return Min(conn, field, @params);
        }

        /// <summary>
        /// Get the miminum value for a specified field
        /// of this database item using a Param for the Where clause
        /// </summary>
        /// <param name=""field"">The Field for which to find the miminum value</param>
        /// <param name=""param"">The Param object used for the
        /// Where clause</param>
        /// <returns>The miminum value found or DbNull</returns>
        public static object Min(this DbConnection conn, Field field, Param ParamForWhereClause)
        {
            List<Param> @params = new List<Param>();
            @params.Add(ParamForWhereClause);
            return Min(conn, field, @params);
        }

        /// <summary>
        /// Get the miminum value for a specified field
        /// of this database item using a collection of Param for the Where clause
        /// </summary>
        /// <param name=""field"">The Field for which to find the miminum value</param>
        /// <param name=""@params"">The Param collection object used for the
        /// Where clause</param>
        /// <returns>The miminum value found or DbNull</returns>
        public static object Min(this DbConnection conn, Field field, IEnumerable<Param> @params)
        {
            using (var command = new " + GetCommandType() + @"()) {
                Param param = new Param(field);
                command.CommandText = ""SELECT MIN("" + param.FieldFormatted + "") FROM " + Escape(item.Schema, item.Name) + @" "";
                command.AddWhereClause(@params);
                return conn.ExecuteScalar(command);
            }
        }

        /// <summary>Gets the row count in this DB item</summary>
        /// <returns>The row count</returns>
        public static long Count(this DbConnection conn)
        {
            return Count(conn, new List<Param>());
        }

        /// <summary>Gets the row count in this DB item</summary>
        /// <param name=""param"">The Param object used for the Where clause</param>
        /// <returns>The row count</returns>
        public static long Count(this DbConnection conn, Param param)
        {
            List<Param> @params = new List<Param>();
            @params.Add(param);
            return Count(conn, @params);
        }

        /// <summary>Gets the row count in this DB item</summary>
        /// <param name=""@params"">The Param collection object used for the Where clause</param>
        /// <returns>The row count</returns>
        public static long Count(this DbConnection conn, IEnumerable<Param> @params)
        {
            using (var command = new " + GetCommandType() + @"()) {
                command.CommandText = ""SELECT COUNT(*) FROM " + Escape(item.Schema, item.Name) + @" "";
                command.AddWhereClause(@params);
                return (long)conn.ExecuteScalar(command);
            }
        }
");
        }

        string GetDataReaderFunction(Type type)
        {
            string type_string = type.ToString();
            switch (type_string) {
            case "System.String":
                return "GetString";
            case "System.Int16":
                return "GetInt16";
            case "System.Int32":
                return "GetInt32";
            case "System.Int64":
                return "GetInt64";
            case "System.Boolean":
                return "GetBoolean";
            }

            return null;
        }

        protected string Escape(string schema, string name)
        {
            return (schema == null ? "" : GetEscapeStart() + schema + GetEscapeEnd() + ".") 
                + GetEscapeStart() + name + GetEscapeEnd();
        }

        protected string Escape(string name)
        {
            return GetEscapeStart() + name + GetEscapeEnd();
        }

        #endregion
    }
}
